﻿USE [RAD_Timesheet1]
GO

/****** Object:  Table [dbo].[Clients]    Script Date: 01/06/2011 00:52:47  Created by Paul Beck ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Clients](
	[ClientId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Active] [bit] NULL,
 CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED 
(
	[ClientId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object:  Table [dbo].[Resource]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Resource](
	[ResourceId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [varchar](255) NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[Active] [bit] NULL,
 CONSTRAINT [Resource_PK] PRIMARY KEY CLUSTERED 
(
	[ResourceId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[WeekEntry]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WeekEntry](
	[WeekEntryId] [int] IDENTITY(1,1) NOT NULL,
	[ResourceId] [int] NOT NULL,
	[WeekStartDate] [datetime] NOT NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[Status] [nchar](12) NULL,
	[Time] [float] NULL,
 CONSTRAINT [WeekEntry_PK] PRIMARY KEY CLUSTERED 
(
	[WeekEntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Project]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Project](
	[ProjectId] [int] IDENTITY(1,1) NOT NULL,
	[ClientId] [int] NULL,
	[Description] [varchar](255) NOT NULL,
	[TotalExpectedHours] [decimal](12, 2) NOT NULL,
	[TotalAllocatedHours] [decimal](12, 2) NOT NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[Active] [bit] NULL,
	[CostCentre] [varchar](20) NULL,
	[ParentProjectId] [int] NULL,
 CONSTRAINT [Project_PK] PRIMARY KEY CLUSTERED 
(
	[ProjectId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ProjectResource]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProjectResource](
	[ProjectResourceId] [int] IDENTITY(1,1) NOT NULL,
	[ResourceId] [int] NOT NULL,
	[ProjectId] [int] NOT NULL,
	[TotalExpectedHours] [decimal](12, 2) NOT NULL,
	[TotalAllocatedHours] [decimal](12, 2) NOT NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
 CONSTRAINT [ResourceProject_PK] PRIMARY KEY CLUSTERED 
(
	[ProjectResourceId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DayEntry]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DayEntry](
	[DayEntryId] [int] IDENTITY(1,1) NOT NULL,
	[WeekEntryId] [int] NOT NULL,
	[DayDate] [datetime] NOT NULL,
	[DayNumber] [int] NOT NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[Time] [float] NULL,
 CONSTRAINT [DayEntry_PK] PRIMARY KEY CLUSTERED 
(
	[DayEntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO


/****** Object:  StoredProcedure [dbo].[sp_GetTimeforWeekUsingDays]    Script Date: 01/06/2011 00:52:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetTimeforWeekUsingDays]
   @weekId  int
AS
  
SELECT SUM([Time])
  FROM [RAD_Timesheet1].[dbo].DayEntry
  WHERE WeekEntryId = @weekId
GO


/****** Object:  Table [dbo].[ResourceProjectTime]    Script Date: 01/06/2011 00:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ResourceProjectTime](
	[ResourceProjectTimeId] [int] IDENTITY(1,1) NOT NULL,
	[ProjectResourceId] [int] NOT NULL,
	[DayEntryIdFk] [int] NULL,
	[Notes] [varchar](4000) NULL,
	[CreatedBy] [varchar](255) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [varchar](255) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[Time] [float] NULL,
 CONSTRAINT [ResourceProjectTime_PK] PRIMARY KEY CLUSTERED 
(
	[ResourceProjectTimeId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO

/****** Object:  StoredProcedure [dbo].[sp_ResourceProjectTimeSumTimeByResource]    Script Date: 01/06/2011 00:52:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ResourceProjectTimeSumTimeByResource] 
   @prjResId  int
AS
SELECT SUM([Time])
  FROM [RAD_Timesheet1].[dbo].[ResourceProjectTime]
  WHERE ProjectResourceId = @prjResId
GO

/****** Object:  StoredProcedure [dbo].[sp_ResourceProjectTimeSumTime]    Script Date: 01/06/2011 00:52:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ResourceProjectTimeSumTime] 
   @dayId  float
AS
SELECT SUM([Time])
  FROM [RAD_Timesheet1].[dbo].[ResourceProjectTime]
  WHERE DayEntryIdFk = @dayId
GO

/****** Object:  Default [DF_Clients_Active]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_Active]  DEFAULT ((1)) FOR [Active]
GO
/****** Object:  Default [DF_Project_Active]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[Project] ADD  CONSTRAINT [DF_Project_Active]  DEFAULT ((1)) FOR [Active]
GO



/****** Object:  Default [DF_Project_ParentProjectId]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[Project] ADD  CONSTRAINT [DF_Project_ParentProjectId]  DEFAULT ((0)) FOR [ParentProjectId]
GO
/****** Object:  Default [DF_Resource_Active]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[Resource] ADD  CONSTRAINT [DF_Resource_Active]  DEFAULT ((1)) FOR [Active]
GO

/****** Object:  ForeignKey [WeekEntry_DayEntry_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[DayEntry]  WITH CHECK ADD  CONSTRAINT [WeekEntry_DayEntry_FK1] FOREIGN KEY([WeekEntryId])
REFERENCES [dbo].[WeekEntry] ([WeekEntryId])
GO
ALTER TABLE [dbo].[DayEntry] CHECK CONSTRAINT [WeekEntry_DayEntry_FK1]
GO



/****** Object:  ForeignKey [FK_Project_Clients]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[Project]  WITH CHECK ADD  CONSTRAINT [FK_Project_Clients] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([ClientId])
GO
ALTER TABLE [dbo].[Project] CHECK CONSTRAINT [FK_Project_Clients]
GO



/****** Object:  ForeignKey [Project_ResourceProject_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[ProjectResource]  WITH CHECK ADD  CONSTRAINT [Project_ResourceProject_FK1] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Project] ([ProjectId])
GO
ALTER TABLE [dbo].[ProjectResource] CHECK CONSTRAINT [Project_ResourceProject_FK1]
GO

/****** Object:  ForeignKey [Resource_ResourceProject_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[ProjectResource]  WITH CHECK ADD  CONSTRAINT [Resource_ResourceProject_FK1] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([ResourceId])
GO
ALTER TABLE [dbo].[ProjectResource] CHECK CONSTRAINT [Resource_ResourceProject_FK1]
GO

/****** Object:  ForeignKey [DayEntry_ResourceProjectTime_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[ResourceProjectTime]  WITH CHECK ADD  CONSTRAINT [DayEntry_ResourceProjectTime_FK1] FOREIGN KEY([DayEntryIdFk])
REFERENCES [dbo].[DayEntry] ([DayEntryId])
GO
ALTER TABLE [dbo].[ResourceProjectTime] CHECK CONSTRAINT [DayEntry_ResourceProjectTime_FK1]
GO
/****** Object:  ForeignKey [ResourceProject_ResourceProjectTime_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[ResourceProjectTime]  WITH CHECK ADD  CONSTRAINT [ResourceProject_ResourceProjectTime_FK1] FOREIGN KEY([ProjectResourceId])
REFERENCES [dbo].[ProjectResource] ([ProjectResourceId])
GO
ALTER TABLE [dbo].[ResourceProjectTime] CHECK CONSTRAINT [ResourceProject_ResourceProjectTime_FK1]
GO
/****** Object:  ForeignKey [Resource_WeekEntry_FK1]    Script Date: 01/06/2011 00:52:47 ******/
ALTER TABLE [dbo].[WeekEntry]  WITH CHECK ADD  CONSTRAINT [Resource_WeekEntry_FK1] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([ResourceId])
GO
ALTER TABLE [dbo].[WeekEntry] CHECK CONSTRAINT [Resource_WeekEntry_FK1]
GO

/* PB 08 June 2012 - As the default resource is reported as an issue so often when implementing the Timesheet I have added a default row for the system account 
Feel free to remove this insert statement.   */
INSERT INTO [dbo].[Resource] (Name, CreatedBy, CreatedDate, Active)
     VALUES ('System Account', 'Paul Beck', '01/01/2012', 'True')
GO